Dear Tom,
first of all thank You for Your response. But here is the
reality. Table formula have about 450000 rows at this time.
The id_formula column is int4 defined as not null primary key. And the index
have clause unique, but backend still don't use it. I have another column in
this table named id_loan. It's int4 too, but it's not unique, but there are 18
to 50 rows with the same value in this column. I have tried to create index
using btree or hash on this column, the index was created, but backend don't
use it. Even i add id_loan = id_loan to SELECT, then backend use the index. I
can't understand to that. If you will need other informations, please ask me.
Thank You very much
Zdenek Habala
>
> Od : Tom Lane <tgl@sss.pgh.pa.us>
> Datum : 2001/08/07 Út dop. 01:55:56 GMT+02:00
> Komu : zhabala@telecom.cz, pgsql-bugs@postgresql.org
> Předmět : Re: [BUGS] backend don't use index when querying by indexed column
>
> pgsql-bugs@postgresql.org writes:
> > backend don't use index when querying by indexed column
>
> This is not necessarily a bug.
>
> > when i try to explain this select i will got this:
>
> > explain select * from formula where id_formula = 1;
> > NOTICE: QUERY PLAN:
> > Seq Scan on formula (cost=0.00..10919.89 rows=4576 width=72)
>
> How many rows altogether in this table? How many actually have
> id_formula = 1? It would appear from the rows=4576 estimate that there's
> at least one value in the table that occurs thousands of times.
>
> > but when i try to explain modified select like this:
> > explain select * from formula where id_formula = 1 and
id_formula=id_formula;
> > NOTICE: QUERY PLAN:
> > Index Scan using predpis_id_formula_key on formula (cost=0.00..11952.57
rows=46
> > width=72)
>
> > backend uses the index.
>
> I think the extra time per-row to evaluate the extra WHERE clause pushes
> up the estimated cost of the seq scan just enough to make it a little
> more expensive than the estimated cost of the indexscan (note that
> they're pretty close in the two examples). The seqscan will have to
> evaluate two operators for every row, whereas the indexscan only has to
> do it at the rows found by the index, so its cost goes up less when you
> add more WHERE conditions.
>
> If these estimates have nothing to do with reality in your situation,
> then that's a bug. But you haven't told us anything about what reality
> is. If the planner's estimates are correct then it's doing the right
> thing.
>
> regards, tom lane
>